package com.yy.base.common.dao;

import com.yy.base.common.dialect.PostgresqlTransformer;
import com.yy.base.page.Page;
import com.yy.base.util.ObjectUtil;
import com.yy.base.util.SessionInfo;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.springframework.dao.support.DaoSupport;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
@SuppressWarnings("unchecked")
public class HibernateDao extends DaoSupport implements Dao {
	private SessionFactory sessionFactory;
	
	public void delete(Object obj) {
		this.getSession().delete(obj);
	}
	public void save(Object obj) {
		this.getSession().save(obj);
	}
	public void update(Object obj) {
		this.getSession().update(obj);
	}
	
	public <T> T get(Class<T> clz, Serializable id) {
		return (T)this.getSession().get(clz, id);
	}
	/**
	 * 非分页hql查询
	 */
	public List find(String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		return query.list();
	}
	/**
	 * hql查询转换为Map集合
	 */
	public List findToMap(String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.list();
	}
	/**
	 * hql查询转换为实体集合
	 */
	public <T> List<T> findToEntity(Class<T> clzz, String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		//讲查询结果转化为实体结果
		query.setResultTransformer(Transformers.aliasToBean(clzz));
		return query.list();
	}
	/**
	 * 通过hql语句查询结果
	 */
	public Object findUnique(String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		return query.uniqueResult();
	}
	/**
	 * 分页hql查询
	 */
	public List splitFind(String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		Page page = SessionInfo.getPage();
		page.setTotal(countHql(hql, objs));
		
		return query.setFirstResult((page.getPageIndex() - 1) * page.getPageSize())
				.setMaxResults(page.getPageSize()).list();
	}
	/**
	 * 分页hql查询
	 */
	public List<Map> splitFindToMap(String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		Page page = SessionInfo.getPage();
		page.setTotal(countHql(hql, objs));
		
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.setFirstResult((page.getPageIndex() - 1) * page.getPageSize())
				.setMaxResults(page.getPageSize()).list();
	}
	/**
	 * 统计查询语句总记录数
	 */
	private Long countHql(String hql, Object... objs) {
		String sql1 = hql;
		String upSql = hql.toUpperCase();
		int order = upSql.lastIndexOf(" ORDER ");
		if (order > hql.lastIndexOf(")")) {
			sql1 = sql1.substring(0, order);
		}
		if (upSql.startsWith(" FROM") || upSql.startsWith("FROM"))
			sql1 = "select count(*) " + sql1;
		else
			sql1 = "select count(*) from (" + sql1 + ") tb";
		return getHqlCount(sql1, objs);
	}
	/**
	 * 获得Hql的统计结果
	 */
	public Long getHqlCount(String hql, Object... objs) {
		Query query = this.getSession().createQuery(hql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		Object value = query.uniqueResult();
		if (value != null)
			return Long.parseLong(value.toString());
		return null;
	}
	/**
	 * 执行sql更新语句
	 */
	public int executeSql(String sql, Object... objs) {
		Query query = this.getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		return query.executeUpdate();
	}
	/**
	 * 执行sql更新语句
	 */
	public int executeSql(String sql, Map params) {
		Query query = this.getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		return query.executeUpdate();
	}
	/**
	 * sql查询
	 * 返回Map，key为大写
	 */
	public List<Map<String, Object>> query(String sql, Object... objs) {
		Query query = this.getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.list();
	}
	/**
	 * 通过sql语句查询结果
	 */
	public<T> List<T> query(Class<T> clzz, String sql, Object... objs) {
		List<Map<String, Object>> list = query(sql, objs);
		return ObjectUtil.listMapToEntity(clzz, list);
	}
	/**
	 * 通过sql语句查询结果
	 */
	public List<Map<String, Object>> query(String sql, Map params) {
		Query query = this.getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.list();
	}
	/**
	 * 通过sql语句查询结果
	 */
	public Map<String, Object> queryUnique(String sql, Object... objs) {
		List<Map<String, Object>> list = query(sql, objs);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 通过sql语句查询结果
	 */
	public<T> T queryUnique(Class<T> clzz, String sql, Object... objs) {
		return ObjectUtil.mapToEntity(clzz, queryUnique(sql, objs));
	}
	/**
	 * 分页sql查询
	 * 返回Map，key为大写
	 */
	public List<Map<String, Object>> splitQuery(String sql, Object... objs) {
		Query query = this.getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		Page page = SessionInfo.getPage();
		page.setTotal(countSql(sql, objs));
		
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.setFirstResult((page.getPageIndex() - 1) * page.getPageSize())
					.setMaxResults(page.getPageSize()).list();
	}
	/**
	 * 分页sql查询
	 * 返回Map，key为大写
	 */
	public List<Map<String, Object>> splitQuery(String sql, Map params) {
		Query query = this.getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		Page page = SessionInfo.getPage();
		page.setTotal(countSql(sql, params));
		
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.setFirstResult((page.getPageIndex() - 1) * page.getPageSize())
					.setMaxResults(page.getPageSize()).list();
	}
	/**
	 * 获得查询语句的总记录数
	 */
	private Long countSql(String sql, Map params) {
		String sql1 = sql;
		String upSql = sql.toUpperCase();
		int order = upSql.lastIndexOf(" ORDER ");
		if (order > sql.lastIndexOf(")")) {
			sql1 = sql1.substring(0, order);
		}
		return getSqlCount("select count(*) from (" + sql1 + ") tb", params);
	}
	/**
	 * 获得查询语句的总记录数
	 */
	private Long countSql(String sql, Object... objs) {
		String sql1 = sql;
		String upSql = sql.toUpperCase();
		int order = upSql.lastIndexOf(" ORDER ");
		if (order > sql.lastIndexOf(")")) {
			sql1 = sql1.substring(0, order);
		}
		return getSqlCount("select count(*) as count0 from (" + sql1 + ") as tb", objs);
	}

	/**
	 * 获得sql的统计结果
	 */
	public Long getSqlCount(String sql, Object... objs) {
		Query query = this.getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		Object value = query.uniqueResult();
		if (value != null)
			return Long.parseLong(value.toString());
		return null;
	}
	/**
	 * 获得sql的统计结果
	 */
	public Long getSqlCount(String sql, Map params) {
		Query query = this.getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		Object value = query.uniqueResult();
		if (value != null)
			return Long.parseLong(value.toString());
		return null;
	}
	/**
	 * 传入实体对象、表名保存数据
	 * obj： 实体对象
	 * table：表名
	 */
	public int save(Object obj, String table) {
		SqlUtil util = new SqlUtil();
		String sql = util.createInsert(obj, table);
		List paramList = util.getParams();
		int result = 0;

		Query query = this.getSession().createSQLQuery(sql);
		int size = paramList.size();
		if (size > 0) {
			for (int i = 0; i < size; i++) {
				query.setParameter(i, paramList.get(i));
			}
		}
		result = query.executeUpdate();
		return result;
	}
	/**
	 * 传入实体对象、表名修改数据
	 * obj： 实体对象
	 * table：表名
	 */
	public int update(Object obj, String table) {
		return update(obj, table, null);
	}
	/**
	 * 传入实体对象、表名修改数据
	 * obj： 实体对象
	 * table：表名
	 * idName: 主键属性
	 */
	public int update(Object obj, String table, String idName) {
		SqlUtil util = new SqlUtil();
		String sql = util.createUpdate(obj, table, idName);
		List paramList = util.getParams();
		int result = 0;

		Query query = this.getSession().createSQLQuery(sql);
		int size = paramList.size();
		if (size > 0) {
			for (int i = 0; i < size; i++) {
				query.setParameter(i, paramList.get(i));
			}
		}
		result = query.executeUpdate();
		return result;
	}


	/**
	 * 执行存储过程函数
	 * @param sql
	 * @param parms
	 */
	public void callSql(final String sql, final Object...parms) {
		this.getSession().doWork(new Work() {
			public void execute(Connection connection) throws SQLException {
				CallableStatement ps = null;
				try {
					ps = connection.prepareCall(sql);
					if (parms != null) {
						for (int i = 0; i < parms.length; i++) {
							ps.setObject(i, parms[i]);
						}
					}
					ps.execute();
				} finally {
					try {
						ps.close();
					} catch (Exception e) {
					}
				}
			}
		});
	}
	/**
	 * 得到表字段属性
	 * 包括：
	 * name：列名
	 * isNotNull：字段是否能为空
	 * autoAdd：是否自动增长
	 * length：字段长度
	 * text：字段说明
	 * type：字段在数据库的类型名称
	 * signed：
	 * readOnly：只读
	 */
	public List<Map> getFields(final String table) {
		final List list = new ArrayList();
		this.getSession().doWork(new Work() {
			public void execute(Connection connection) {
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				String sql = "select * from " + table + " where 1=2";
				ps = connection.prepareStatement(sql);
				rs = ps.executeQuery();
				Map map = null;
				ResultSetMetaData rsmd = ps.getMetaData();
				int size = rsmd.getColumnCount();
				String name = null;

				for (int i = 1; i <= size; i++) {
					try {
						map = new HashMap();
						map.put("name", rsmd.getColumnName(i));
						name = rsmd.getSchemaName(i);
						map.put("text", name == null || "".equals(name) ? rsmd.getColumnLabel(i) : name);
						map.put("type", rsmd.getColumnTypeName(i));
						map.put("length", rsmd.getColumnDisplaySize(i));
						map.put("isNotNull", rsmd.isNullable(i));
						map.put("fixed", rsmd.getScale(i));//保留小数点位数
						map.put("autoAdd", rsmd.isAutoIncrement(i));
						map.put("readOnly", rsmd.isReadOnly(i));
						map.put("signed", rsmd.isSigned(i));
						list.add(map);
					} catch (Exception e) {
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (Exception e) {
				}
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
			}
		});
		return list;
	}
	protected void checkDaoConfig() throws IllegalArgumentException {
	}

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	@Resource
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	private Session getSession() {
		if (this.sessionFactory == null) {
			throw new HibernateException("Session创建失败，SessionFactory为null!");
		}
		return this.sessionFactory.getCurrentSession();
	}

	protected final void releaseSession(Session session) {
		SessionFactoryUtils.closeSession(session);
		if (null != session) {
			session = null;
		}
	}
}